package com.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xwpf.usermodel.XWPFDocument;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;

public class ImportAndExportXls {

    /**
     * 生成excel并下载
     */
    public void exportExcel() {

        File newFile = createNewFile();
        //File newFile = new File("d:/ss.xls");

        //新文件写入数据，并下载*****************************************************
        InputStream is = null;
        HSSFWorkbook workbook = null;
        HSSFSheet sheet = null;
        try {
            is = new FileInputStream(newFile);
            workbook = new HSSFWorkbook(is);
            //获取第一个sheet
            sheet = workbook.getSheetAt(0);
        } catch (Exception e1) {
            e1.printStackTrace();
        }

        if (sheet != null) {
            try {
                //写数据
                FileOutputStream fos = new FileOutputStream(newFile);
//                HSSFRow row = sheet.getRow(1);
//                HSSFCell cell = row.getCell(0);
//                System.out.println(cell.getStringCellValue());
//                cell.setCellValue("ssssssssssssssssssssssssssssssssssssssssssss");
                // 在指定的索引处创建一行
                HSSFRow row = sheet.createRow(1);
                row.createCell(0).setCellValue("ssssssssssssssssssssssssssssssssssssssssssss");
                workbook.write(fos);
                fos.flush();
                fos.close();

                //默导出到流
//                response.setContentType("application/vnd.ms-excel");
//                response.setHeader("Content-disposition", "attachment;filename=" + System.currentTimeMillis() + ".xlsx");
//                OutputStream out = response.getOutputStream();
//                workbook.write(out);
//                out.flush();
//                out.close();
                //下载
//                InputStream fis = new BufferedInputStream(new FileInputStream(newFile));
//                HttpServletResponse response = this.getResponse();
//                byte[] buffer = new byte[fis.available()];
//                fis.read(buffer);
//                fis.close();
//                response.reset();
//                response.setContentType("text/html;charset=UTF-8");
//                OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
//                response.setContentType("application/x-msdownload");
//                String newName = URLEncoder.encode("采购合同"+System.currentTimeMillis()+".xls", "UTF-8");
//                response.addHeader("Content-Disposition", "attachment;filename=\""+ newName + "\"");
//                response.addHeader("Content-Length", "" + newFile.length());
//                toClient.write(buffer);
//                toClient.flush();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    is.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        //删除创建的新文件
//        this.deleteFile(newFile);
    }

    /**
     * 复制文件
     *
     * @param s 源文件
     * @param t 复制到的新文件
     */

    public void fileChannelCopy(File s, File t) {
        try {
            InputStream in = null;
            OutputStream out = null;
            try {
                in = new BufferedInputStream(new FileInputStream(s), 1024);
                out = new BufferedOutputStream(new FileOutputStream(t), 1024);
                byte[] buffer = new byte[1024];
                int len;
                while ((len = in.read(buffer)) != -1) {
                    out.write(buffer, 0, len);
                }
            } finally {
                if (null != in) {
                    in.close();
                }
                if (null != out) {
                    out.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取excel模板，并复制到新文件中供写入和下载
     *
     * @return
     */
    public File createNewFile() {
        //读取模板，并赋值到新文件************************************************************
        //文件模板路径
//        String path = this.getRequest().getRealPath(SystemConfig.FILETEMPLATE);
        String path = this.getClass().getResource("/template").getPath();
        String fileName = "1000113200023646545_201801061208_预分配模板.xls";
        File file = new File(path + "/" + fileName);
//        URL url = this.getClass().getClassLoader().getResource("/template/"+fileName);
//        File file = new File(url.getFile());
        //保存文件的路径
//        String realPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.UPLOAD_FILE_DIR);
        String realPath = this.getClass().getResource("/template").getPath();
        //新的文件名
        String newFileName = "预分配信息" + System.currentTimeMillis() + ".xls";
        //判断路径是否存在
        File dir = new File(realPath);
        if (!dir.exists()) {
            dir.mkdirs();
        }
        //写入到新的excel
        File newFile = new File(realPath, newFileName);
        try {
            newFile.createNewFile();
            //复制模板到新文件
            fileChannelCopy(file, newFile);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return newFile;
    }


    /**
     * 方法名：setBrowser
     * 功能：使用浏览器下载
     */
    private void setBrowser(HttpServletRequest request, HttpServletResponse response, XWPFDocument document, String fileName) {
        OutputStream os = null;
        try {
            //清空response
            response.reset();
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-word;charset=UTF-8");


            //将word写入到输出流中
            document.write(os);
            os.flush();
            os.close();
            System.out.println("设置浏览器下载成功！");
        } catch (Exception e) {
            System.out.println("设置浏览器下载失败！");
            e.printStackTrace();
        } finally {
            try {
                if (os != null) os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    //todo 测试
    public static void main(String args[]) throws Exception {
        ImportAndExportXls exportExcelService = new ImportAndExportXls();

        exportExcelService.exportExcel();
    }
}
